﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;


public class QHSEExcelService
{
    private readonly SimpleDbContext _context;
    private readonly QHSEDictionaryService _dictionaryService;
    private readonly QHSEKeyWorkSpecialtyService _keyWorkSpecialtyService;
    private readonly QHSEKeyWorkTypeService _keyWorkTypeService;
    private readonly QHSEDangerCategoryService _categoryService;
    private readonly QHSEDangerCategoryHSEService _categoryHSEService;
    private readonly QHSEXySpecialtyViolationService _generalViolationService;
    private readonly QHSEViolationClauseAssocitionService _violationClauseAssocitionService;
    private readonly QHSELocationService _locationService;
    private  readonly QHSEPositionService _positionService;
    private readonly QHSEViolationHSEService _violationHSEService;

    public QHSEExcelService(SimpleDbContext context, QHSEDictionaryService dictionaryService, QHSEKeyWorkSpecialtyService keyWorkSpecialtyService, QHSEKeyWorkTypeService keyWorkTypeService, QHSEDangerCategoryService categoryService, QHSEDangerCategoryHSEService categoryHSEService,QHSEXySpecialtyViolationService generalViolationService, QHSEViolationClauseAssocitionService violationClauseAssocitionService, QHSELocationService locationService, QHSEPositionService positionService, QHSEViolationHSEService violationHSEService)
    {
        _context = context;
        _dictionaryService = dictionaryService;
        _keyWorkSpecialtyService = keyWorkSpecialtyService;
        _keyWorkTypeService = keyWorkTypeService;
        _categoryService = categoryService;
        _categoryHSEService = categoryHSEService;
        _generalViolationService = generalViolationService;
        _violationClauseAssocitionService = violationClauseAssocitionService;
        _locationService = locationService;
        _positionService = positionService;
        _violationHSEService = violationHSEService;
    }
    public byte[] UploadExcel()
    {
        // 设置 ExcelPackage 的 LicenseContext
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        // 创建一个新的Excel包
        using (ExcelPackage package = new ExcelPackage())
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

            // 写入表头并设置字体加粗
            for (int col = 1; col <= 20; col++)
            {
                ExcelRange headerCell = worksheet.Cells[1, col];
                headerCell.Value = GetDangerHeaderTitle(col);
                headerCell.Style.Font.Bold = true;
            }

            // 设置列宽
            for (int i = 1; i <= 20; i++)
            {
                worksheet.Column(i).AutoFit();
            }

            // 生成Excel文件的字节数组
            byte[] fileBytes = package.GetAsByteArray();
            return fileBytes;
        }
    }
    private string GetDangerHeaderTitle(int columnIndex)
    {
        switch (columnIndex)
        {
            case 1: return "检查时间";
            case 2: return "检查类型";
            case 3: return "作业专业";
            case 4: return "作业项目";
            case 5: return "关键作业类型";
            case 6: return "关键作业内容";
            case 7: return "作业风险等级";
            case 8: return "位置区域";
            case 9: return "隐患条款";
            case 10: return "隐患级别";
            case 11: return "隐患描述";
            case 12: return "隐患类别";
            case 13: return "隐患分类";
            case 14: return "原因分析";
            case 15: return "归属部门";
            case 16: return "整改负责人";
            case 17: return "负责人电话/邮箱";
            case 18: return "限期整改时间";
            case 19: return "隐患状态";
            case 20: return "验收时间";
            case 21: return "验收描述";
            case 22: return "体系要素";
            default: return string.Empty;
        }
    }


    public async Task<PageResultModel<ImportDangerModel>> ImportDangerExcel(QHSEDangerUploadModel model)
    {
        List<ImportDangerModel> importedData = new List<ImportDangerModel>();
        byte[] fileBytes = Convert.FromBase64String(model.file.Base64Content);
        // 检查文件字节数组是否为空
        if (fileBytes == null || fileBytes.Length == 0)
        {
            throw new ArgumentNullException(nameof(fileBytes), "文件字节数组为空。");
        }

        // 解析Excel文件
        using (MemoryStream stream = new MemoryStream(fileBytes))
        using (ExcelPackage package = new ExcelPackage(stream))
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();

            if (worksheet == null)
            {
                throw new Exception("Excel文件中未找到工作表。");
            }

            int rowCount = worksheet.Dimension.Rows;

            // 获取列标题
            List<string> columnTitles = new List<string>();
            for (int col = 1; col <= worksheet.Dimension.Columns; col++)
            {
                string title = GetDangerHeaderTitle(col);
                if(!string.IsNullOrEmpty(title))
                {
                    columnTitles.Add(title);
                }
                
            }
            var dic = await _dictionaryService.GetDictionaryByItemAsync();
            var dicSp = await _keyWorkSpecialtyService.GetDictionaryByItemAsync();
            // 从第二行开始解析数据，第一行为表头
            for (int row = 4; row <= rowCount; row++)
            {
                ImportDangerModel data = new ImportDangerModel();

                // 从Excel中读取数据并设置到数据对象中
                for (int col = 1; col <= columnTitles.Count; col++)
                {
                    string columnTitle = columnTitles[col - 1];
                    string cellValue = worksheet.Cells[row, col].Value?.ToString();

                    // 根据列标题设置相应属性
                    SetDangerPropertyValue(data, columnTitle, cellValue);
                }
                data.EnterSys = "公司隐患违章系统";
                data.UserId = model.UserId;
                data.CheckName = model.RealName;
                data.OrganizationId = model.DangerOrganizationId;
                data.OrganizationName = model.DangerOrganizationName;
                data.CheckOrganizationId = model.CheckOrganizationId;
                data.ThirdOrganizationId = model.ThirdOrganizationId;
                data.SecondOrganizationId = model.SecondOrganizationId;
                if (!string.IsNullOrEmpty(data.CheckType))
                {
                    data.CheckTypeCode = dic.GetValueOrDefault(data.CheckType);
                }
                if (!string.IsNullOrEmpty(data.DirectDepartment))
                {
                    data.DepartmentCode = dic.GetValueOrDefault(data.DirectDepartment);
                }
                if (!string.IsNullOrEmpty(data.CauseAnalysis))
                {
                    data.ClauseAnalysisCode = dic.GetValueOrDefault(data.CauseAnalysis);
                }
                if (!string.IsNullOrEmpty(data.DangerType))
                {
                    data.DangerTypeCode = dic.GetValueOrDefault(data.DangerType);
                }
                if (!string.IsNullOrEmpty(data.ReformerUserPhone))
                {
                    var user = await _context.Set<QHSESysUser>()
                        .AsNoTracking()
            .FirstOrDefaultAsync(u => u.EmployeeNo == data.ReformerUserPhone || u.Email == data.ReformerUserPhone || u.Mobile == data.ReformerUserPhone
            || u.IdCard == data.ReformerUserPhone);
                    if (user != null)
                    {
                        data.ReformerUserId = user.UserId;
                    }
                    else
                    {
                        data.ReformerUserId = data.ReformerUserName;
                    }
                }
                if (!string.IsNullOrEmpty(data.WorkSpecialty))
                {
                    data.WorkSpecialtyCode = dicSp.GetValueOrDefault(data.WorkSpecialty);
                }
                if (!string.IsNullOrEmpty(data.WorkType))
                {
                    data.KeyWorkCode = await _keyWorkTypeService.GetCodeByItemAsync(data.WorkType);
                }
                if (!string.IsNullOrEmpty(data.DangerCategory) && data.DangerCategory != "其它")
                {
                    
                    var  id= await _categoryService.GetIdByItemAsync(data.DangerCategory);
                    if (string.IsNullOrEmpty(id))
                    {
                        data.DangerCategory = string.Empty;
                    }
                    else
                    {
                        data.DangerCateId = id;
                        QHSEDangerCategoryHSEPageInputModel inputModel = new QHSEDangerCategoryHSEPageInputModel();
                        inputModel.Id = data.DangerCateId;
                        var hseResult = await _categoryHSEService.GetResult(inputModel);
                        if (hseResult!= null)
                        {
                            data.HseCode = hseResult.Result.First().Code;
                            data.HseElement = hseResult.Result.First().Item;
                        }
                    }
                    
                }
                if (!string.IsNullOrEmpty(data.HseElement))
                {
                    data.HseCode = await _violationHSEService.GetCodeByItemAsync(data.HseElement);
                }
                importedData.Add(data);
            }
        }
        var page = new PageResultModel<ImportDangerModel>();
        page.Rows = importedData;
        page.TotalRows = importedData.Count;
        page.SetPage(model);
        page.TotalPage = page.CountTotalPage();
        return page;
    }

    // 根据属性名称设置属性值的方法
    private void SetDangerPropertyValue(ImportDangerModel data, string propertyName, string cellValue)
    {
        string[] formats = { "yyyy/M/d H:m:s", "M/d/yyyy H:m:s", "M/d/yyyy", "yyyy/M/d", "M/d/yyyy H:mm:ss" };
        DateTime parsedDate;
        switch (propertyName)
        {
            case "检查时间":
                if (cellValue != null)
                {
                    bool success = DateTime.TryParseExact(cellValue, formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDate);
                    if (success)
                    {
                        data.CheckDate = parsedDate;
                    }
                }
                break;
            case "检查类型":
                data.CheckType = cellValue;
                break;
            case "作业专业":
                data.WorkSpecialty = cellValue;
                break;
            case "作业项目":
                data.WorkItem = cellValue;
                break;
            case "关键作业类型":
                data.WorkType = cellValue;
                break;
            case "关键作业内容":
                data.WorkContent = cellValue;
                break;
            case "作业风险等级":
                data.WorkRisk = cellValue;
                break;
            case "位置区域":
                data.Location = cellValue;
                break;
            case "隐患级别":
                data.DangerType = cellValue;
                break;
            case "隐患描述":
                data.Description = cellValue;
                break;
            case "隐患类别":
                data.DangerCategory = cellValue;
                break;
            case "隐患分类":
                data.DangerTypeCategory = cellValue;
                break;
            case "原因分析":
                data.CauseAnalysis = cellValue;
                break;
            case "归属部门":
                data.DirectDepartment = cellValue;
                break;
            case "隐患条款":
                data.DangerClause = cellValue;
                break;
            case "整改负责人":
                data.ReformerUserName = cellValue;
                break;
            case "负责人电话/邮箱":
                data.ReformerUserPhone = cellValue;
                break;
            case "限期整改时间":
                if (cellValue != null)
                {
                    bool success = DateTime.TryParseExact(cellValue, formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDate);
                    if (success)
                    {
                        data.ReformerDate = parsedDate;
                    }
                }
                break;
            case "隐患状态":
                data.Status= cellValue;
                break;
            case "验收时间":
                if (cellValue != null)
                {
                    bool success = DateTime.TryParseExact(cellValue, formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDate);
                    if (success)
                    {
                        data.AcceptanceDate = parsedDate;
                    }
                }
                break;
            case "验收描述":
                data.CheckDescription = cellValue;
                break;
            case "体系要素":
                data.HseElement = cellValue;
                break;
            default:
                break;
        }
    }

    private string GetViolationHeaderTitle(int columnIndex)
    {
        switch (columnIndex)
        {
            case 1: return "检查时间";
            case 2: return "检查类型";
            case 3: return "作业专业";
            case 4: return "作业项目";
            case 5: return "关键作业类型";
            case 6: return "关键作业内容";
            case 7: return "作业风险等级";
            case 8: return "位置区域";
            case 9: return "违章人员姓名";
            case 10: return "违章人员手机号或邮箱";
            case 11: return "违章人员单位名称";
            case 12: return "违章岗位";
            case 13: return "年龄";
            case 14: return "工龄";
            case 15: return "用工性质";
            case 16: return "违章条款";
            case 17: return "违章描述";
            case 18: return "原因分析";
            case 19: return "归属直线部门";
            case 20: return "可能后果";
            default: return string.Empty;
        }
    }


    public async Task<PageResultModel<ImportViolationModel>> ImportViolationExcel(QHSEViolationUploadModel model)
    {
        List<ImportViolationModel> importedData = new List<ImportViolationModel>();
        byte[] fileBytes = Convert.FromBase64String(model.file.Base64Content);

        // 检查文件字节数组是否为空
        if (fileBytes == null || fileBytes.Length == 0)
        {
            throw new ArgumentNullException(nameof(fileBytes), "文件字节数组为空。");
        }

        // 解析Excel文件
        using (MemoryStream stream = new MemoryStream(fileBytes))
        using (ExcelPackage package = new ExcelPackage(stream))
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();

            if (worksheet == null)
            {
                throw new Exception("Excel文件中未找到工作表。");
            }

            int rowCount = worksheet.Dimension.Rows;
            var dic = await _dictionaryService.GetDictionaryByItemAsync();
            var dicSp = await _keyWorkSpecialtyService.GetDictionaryByItemAsync();
            // 获取列标题
            List<string> columnTitles = new List<string>();
            for (int col = 1; col <= worksheet.Dimension.Columns; col++)
            {
                string title = GetViolationHeaderTitle(col);
                if (!string.IsNullOrEmpty(title))
                {
                    columnTitles.Add(title);
                }

            }

            // 从第二行开始解析数据，第一行为表头
            for (int row = 4; row <= rowCount; row++)
            {
                ImportViolationModel data = new ImportViolationModel();

                // 从Excel中读取数据并设置到数据对象中
                for (int col = 1; col <= columnTitles.Count; col++)
                {
                    string columnTitle = columnTitles[col - 1];
                    string cellValue = worksheet.Cells[row, col].Value?.ToString();

                    // 根据列标题设置相应属性
                    SetViolationPropertyValue(data, columnTitle, cellValue);
                }
                data.EnterSys = "公司隐患违章系统";
                data.UserId = model.UserId;
                data.UserName = model.RealName;
                data.OrganizationId = model.ViolationOrganizationId;
                data.OrganizationName = model.ViolationOrganizationName;
                data.CheckOrganizationId = model.CheckOrganizationId;
                data.ThirdOrganizationId = model.ThirdOrganizationId;
                data.SecondOrganizationId = model.SecondOrganizationId;
                if (!string.IsNullOrEmpty(data.Check_Type))
                {
                    data.CheckTypeCode = dic.GetValueOrDefault(data.Check_Type);
                }
                if (!string.IsNullOrEmpty(data.DirectDepartment))
                {
                    data.DepartmentCode = dic.GetValueOrDefault(data.DirectDepartment);
                }
                if (!string.IsNullOrEmpty(data.PotentialConsequence))
                {
                    data.ConsequencesCode = dic.GetValueOrDefault(data.PotentialConsequence);
                }
                if (!string.IsNullOrEmpty(data.CauseAnalysis))
                {
                    data.ClauseAnalysisCode = dic.GetValueOrDefault(data.CauseAnalysis);
                }
                if (!string.IsNullOrEmpty(data.Age))
                {
                    data.AgeCode = dic.GetValueOrDefault(data.Age);
                }
                if (!string.IsNullOrEmpty(data.ViolationUserPhone))
                {
                    data.Erp = data.ViolationUserPhone;
                    var user = await _context.Set<QHSESysUser>()
                        .AsNoTracking()
            .FirstOrDefaultAsync(u => u.EmployeeNo == data.ViolationUserPhone || u.Email == data.ViolationUserPhone || u.Mobile == data.ViolationUserPhone
            || u.IdCard == data.ViolationUserPhone);
                    if (user != null)
                    {
                        data.ViolationUserId = user.UserId;
                    }
                    else
                    {
                        data.ViolationUserId = data.ViolationUserName;
                    }
                }
                if (!string.IsNullOrEmpty(data.EmployeeType))
                {
                    data.EmployeeTypeCode = dic.GetValueOrDefault(data.EmployeeType);
                }
                if (!string.IsNullOrEmpty(data.WorkYears))
                {
                    data.WorkYearsCode = dic.GetValueOrDefault(data.WorkYears);
                }
                if (!string.IsNullOrEmpty(data.WorkSpecialty))
                {
                    data.WorkSpecialtyCode = dicSp.GetValueOrDefault(data.WorkSpecialty);
                }
                if (!string.IsNullOrEmpty(data.WorkType))
                {
                    data.KeyWorkCode = await _keyWorkTypeService.GetCodeByItemAsync(data.WorkType);
                }
                if (!string.IsNullOrEmpty(data.Position))
                {
                    data.PositionCode = await _positionService.GetCodeByItemAsync(data.Position);
                }
                if (!string.IsNullOrEmpty(data.Location))
                {
                    data.LocationCode = await _locationService.GetCodeByItemAsync(data.Location);
                }
                if (!string.IsNullOrEmpty(data.ViolationClause))
                {
                    var clause = await _context.Set<QHSESysXyViolationClause>().AsNoTracking().Where(u => u.Description.Equals(data.ViolationClause)).FirstOrDefaultAsync();
                    if (clause != null)
                    {
                        IdInputModel inputModel = new IdInputModel();
                        inputModel.Id = clause.GeneralSpecialtyId;
                        var result = await _violationClauseAssocitionService.GetClauseResult(inputModel);
                        if (result != null)
                        {
                            data.ViolationType = result.ViolationType;
                            data.ViolationTypeCode = result.ViolationTypeCode;
                            data.ViolationTypeCategory = result.ViolationTypeCategory;
                            data.Score = result.score;
                            data.Fine = result.Fine;
                            data.HseElement = result.HSE.First().Item;
                            data.HseCode = result.HSE.First().Code;
                            if(result.Observe != null && result.Observe.Count!=0)
                            {
                                data.ObserveComm = result.Observe.First().Item1 + ";" + result.Observe.First().Item2;
                            }
                            data.ViolationCategory = result.ViolationCategory;
                            data.ViolationCateId = result.ViolationCategoryId;

                        }

                    
                    }
                }
                importedData.Add(data);
            }
        }
        var page = new PageResultModel<ImportViolationModel>();
        page.Rows = importedData;
        page.TotalRows = importedData.Count;
        page.SetPage(model);
        page.TotalPage = page.CountTotalPage();
        return page;
    }

    // 根据属性名称设置属性值的方法
    private void SetViolationPropertyValue(ImportViolationModel data, string propertyName, string cellValue)
    {
        string[] formats = { "yyyy/M/d H:m:s", "M/d/yyyy H:m:s", "M/d/yyyy", "yyyy/M/d", "M/d/yyyy H:mm:ss" };
        DateTime parsedDate;
        switch (propertyName)
        {
            case "检查时间":
                if (cellValue != null)
                {
                    bool success = DateTime.TryParseExact(cellValue, formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDate);
                    if (success)
                    {
                        data.CheckDate = parsedDate;
                    }
                }
                break;
            case "检查类型":
                data.Check_Type = cellValue;
                break;
            case "作业专业":
                data.WorkSpecialty = cellValue;
                break;
            case "作业项目":
                data.WorkItem = cellValue;
                break;
            case "关键作业类型":
                data.WorkType = cellValue;
                break;
            case "关键作业内容":
                data.WorkContent = cellValue;
                break;
            case "作业风险等级":
                data.WorkRisk = cellValue;
                break;
            case "位置区域":
                data.Location = cellValue;
                break;
            case "违章人员姓名":
                data.ViolationUserName = cellValue;
                break;
            case "违章人员手机号或邮箱":
                data.ViolationUserPhone = cellValue;
                break;
            case "违章人员单位名称":
                data.ViolationUserDepart = cellValue;
                break;
            case "原因分析":
                data.CauseAnalysis = cellValue;
                break;
            case "归属直线部门":
                data.DirectDepartment = cellValue;
                break;
            case "违章岗位":
                data.Position = cellValue;
                break;
            case "年龄":
                data.Age = cellValue;
                break;
            case "工龄":
                data.WorkYears = cellValue;
                break;
            case "用工性质":
                data.EmployeeType = cellValue;
                break;
            case "违章条款":
                data.ViolationClause = cellValue;
                break;
            case "违章描述":
                data.Description = cellValue;
                break;
            case "可能后果":
                data.PotentialConsequence = cellValue;
                break;
            default:
                break;
        }
    }
}
